# app/schedule_stats.py

from flask import Blueprint, request, jsonify, session, abort
from sqlalchemy import func, or_
from datetime import datetime
from . import db
from .models import Schedule
from .schedule_mgmt import login_required

stats_bp = Blueprint('schedule_stats', __name__, url_prefix='/api/schedules')

@stats_bp.route('/stats', methods=['GET'])
@login_required
def stats_schedules():
    """
    GET /api/schedules/stats?
      start_date=YYYY-MM-DD&    （可选，不填则统计所有）
      end_date=YYYY-MM-DD&      （可选，不填则统计所有）
      keyword=kw1&keyword=kw2&...   （可选，多关键字）
      status=已上|未上|all         （默认为 all）
    返回每个学生的排课数量和课时费总额。
    """
    uid = session['user_id']
    # 基础查询：按 student_name 分组，统计条数和费用总和
    q = db.session.query(
        Schedule.student_name.label('student'),
        func.count(Schedule.id).label('count'),
        func.sum(Schedule.fee).label('total_fee')
    ).filter(Schedule.user_id == uid)

    # 1) 日期过滤（不填则统计所有）
    sd = request.args.get('start_date')
    ed = request.args.get('end_date')
    if sd:
        try:
            sd_dt = datetime.strptime(sd, '%Y-%m-%d').date()
            q = q.filter(Schedule.one_date >= sd_dt)
        except ValueError:
            pass
    if ed:
        try:
            ed_dt = datetime.strptime(ed, '%Y-%m-%d').date()
            q = q.filter(Schedule.one_date <= ed_dt)
        except ValueError:
            pass

    # 2) 多关键字过滤
    for kw in request.args.getlist('keyword'):
        like = f"%{kw}%"
        q = q.filter(or_(
            Schedule.student_name.ilike(like),
            Schedule.course.ilike(like),
            Schedule.school.ilike(like)
        ))

    # 3) 状态过滤
    st = request.args.get('status', 'all')
    if st in ('已上', '未上'):
        q = q.filter(Schedule.status == st)

    # 4) 分组并排序
    rows = q.group_by(Schedule.student_name) \
            .order_by(func.count(Schedule.id).desc()) \
            .all()

    # 构造返回 JSON
    result = [
        {
            "student": student,
            "count": cnt,
            "total_fee": float(total_fee or 0)
        }
        for student, cnt, total_fee in rows
    ]

    return jsonify(result), 200
